I'm writing a query to look at orders in a table called Order (I did not name it that).  Unfortunately, this is also a reserved key term in SQL.  In the query in visual studio I put the table name in brackets [Order]. The query is simple

SELECT DISTINCT O.ProjectNo, O.ReportType
FROM            [Order] AS O

The query runs correctly while in VS, but once I upload the report to SSRS server, I get an error

• Query execution failed for dataset 'EDROrders'.  (rsErrorExecutingCommand)
  
  • Invalid object name 'Order'

Any ideas?  I'm on SQL 2

As long as the reserved keyword is enclosed (brackets or double-quotes), there should not be a problem. The invalid object name error means that either 1) account does not have permissions on the table, 2) the table is not in the default schema (a best practice is to schema-qualify specify object names, e.g. [dbo].[Order]) or 3) the table doesn't exist in the database context.

Are you sure Order is in the same database which datasource is trying to connect? Also are you using an expression based connectionstring?

Another scenario where this can occur is Order table not being in dbo schema. It may be that it will in your default schema (say xxx) so that when you just refer it as Order it will work fine whereas when it runs from ReportServer its using another account whose default schema is not same as yours. As such it wont find table as its neither in its default schema not dbo

To check that give it access to your schema (if different from dbo) and make select as below

SELECT DISTINCT O.ProjectNo, O.ReportType
FROM            [schemaname].[Order] AS O